--    Program: 		CAPCMIMP_CE_STATEMENT_HEADERS_ALL.sql
--    Description:  Import CM STATEMENT HEADERS information 
--    Revision:     1st     
--    Parameter: 	
--    Created By:   ANDY MAK
--    Date:         3-MAY-2006

set doc off;      
set verify off;
set serveroutput on size 1000000;
whenever sqlerror exit failure rollback;

declare   
   l_processed_count		number;
   l_rejected_count 		number;
   l_err_msg		      varchar2(2000);
   l_err_msg_tmp			varchar2(2000);
   
   l_statement_id					number;
   
   cursor cur_1 is
      select * from CRST_CE_STATEMENT_HEADERS_ALL
      for update;

begin
   l_processed_count := 0;
   l_rejected_count  := 0;
   l_err_msg := '';
   
   for cur in cur_1 loop   	
     l_err_msg_tmp := '';
     
     select CE_STATEMENT_HEADERS_S.NEXTVAL 
     into l_statement_id
     FROM DUAL;

     update CRST_CE_STATEMENT_HEADERS_ALL     
	set    STATEMENT_HEADER_ID_NEW = l_statement_id
     where  statement_header_id = cur.statement_header_id;

     begin
       -- import data
       insert into ce_statement_headers_all
       (
       statement_header_id, 
	   bank_account_id, 
	   statement_number, 
	   statement_date, 
	   created_by, 
	   creation_date, 
	   last_updated_by,
	   last_update_date, 
	   auto_loaded_flag, 
	   gl_date, 
	   control_begin_balance, 
	   control_total_dr, 
	   control_total_cr, 
	   control_end_balance, 
	   control_dr_line_count, 
	   control_cr_line_count, 
	   currency_code, 
	   last_update_login, 
	   attribute_category,
	   attribute1, 
	   attribute2, 
	   attribute3, 
	   attribute4, 
	   attribute5, 
	   attribute6, 
	   attribute7, 
	   attribute8, 
	   attribute9, 
	   attribute10,
	   attribute11, 
	   attribute12, 
	   attribute13, 
	   attribute14, 
	   attribute15, 
	   statement_complete_flag, 
	   org_id, 
	   doc_sequence_id, 
	   doc_sequence_value, 
	   check_digits, 
	   cashflow_balance, 
	   int_calc_balance             
       )
       values
       (
         l_statement_id, 
	   CAPBRIMP_MAPPING.GetBankACMapping(cur.bank_account_id), 
	   cur.statement_number, 
	   cur.statement_date, 
	   CAPBRIMP_MAPPING.GetUserMapping(cur.CREATED_BY),
	   cur.creation_date, 
         CAPBRIMP_MAPPING.GetUserMapping(cur.LAST_UPDATED_BY),
	   cur.last_update_date, 
	   cur.auto_loaded_flag, 
	   cur.gl_date, 
	   cur.control_begin_balance, 
	   cur.control_total_dr, 
	   cur.control_total_cr, 
	   cur.control_end_balance, 
	   cur.control_dr_line_count, 
	   cur.control_cr_line_count, 
	   cur.currency_code, 
	   cur.last_update_login, 
	   cur.attribute_category,
	   cur.attribute1, 
	   cur.attribute2, 
	   cur.attribute3, 
	   cur.attribute4, 
	   cur.attribute5, 
	   cur.attribute6, 
	   cur.attribute7, 
	   cur.attribute8, 
	   cur.attribute9, 
	   cur.attribute10,
	   cur.attribute11, 
	   cur.attribute12, 
	   cur.attribute13, 
	   cur.attribute14, 
	   cur.attribute15, 
	   cur.statement_complete_flag, 
         CAPBRIMP_MAPPING.GetOrganizationMapping(cur.ORG_ID),
	   cur.doc_sequence_id, 
	   cur.doc_sequence_value, 
	   cur.check_digits, 
	   cur.cashflow_balance, 
	   cur.int_calc_balance      
       );  
     exception
       when others then
     	 l_err_msg_tmp := sqlerrm;
     end;	  	    
     
     if (l_err_msg_tmp = '' or l_err_msg_tmp is null) then
       l_processed_count := l_processed_count + 1;  
     else
       l_rejected_count := l_rejected_count + 1;
       l_err_msg := l_err_msg_tmp;
     end if;
   end loop;

   if l_rejected_count = 0 then
   	 commit;
   else
     rollback;
   end if;
 
    if l_err_msg is null then
        l_err_msg := 'No Error';
    end if;
  
   -- insert into log table
   insert into capcnv_log ( prog_id,process_date, record_processed,record_rejected,record_extracted,err_msg) 
    values ('CAPCMIMP_CE_STATEMENT_HEADERS_ALL', sysdate, l_processed_count, l_rejected_count, l_processed_count-l_rejected_count, l_err_msg );
   
   commit;
exception 
  when others then
    dbms_output.put_line(sqlerrm);
    rollback;  
end;
/
exit
